package dao;

import entity.HouseEntity;
import entity.UserInfoEntity;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class HouseInfoDao {

    // 数据库连接相关信息
    private static final String DB_URL = "jdbc:mysql://localhost:3306/house_manage";
    private static final String DB_USER = "root";
    private static final String DB_PASSWORD = "123456";

    // 建立数据库连接的方法
    private Connection getConnection() throws SQLException {
        return DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
    }

    // 关闭数据库连接的方法
    private void closeConnection(Connection conn, Statement stmt, ResultSet rs) {
        try {
            if (rs != null) {
                rs.close();
            }
            if (stmt != null) {
                stmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    public List<HouseEntity> fetchData(String address, Integer price) throws Exception {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        List<HouseEntity> houses = new ArrayList<>();
        try {
            conn = getConnection();
            String sql = "SELECT * FROM house";
            int addressFlag = 0;
            if(address!=null&&!address.equals("")||price!=null){
                sql+=" WHERE ";
            }
            if(address!=null&&!address.equals("")){
                addressFlag = 1;
                sql+=" address like '%"+address+"%'";
            }
            if(price!=null){
                if(addressFlag==1){
                    sql+=" AND ";
                }
                sql+=" price = "+price;
            }
            stmt = conn.prepareStatement(sql);
            rs = stmt.executeQuery();
            // 处理查询结果
            while (rs.next()) {
                HouseEntity house = new HouseEntity();
                house.setId(rs.getInt("id"));
                house.setAddress(rs.getString("address"));
                house.setPrice(rs.getInt("price"));
                house.setArea(rs.getInt("area"));
                house.setPublishTime(rs.getString("publish_time"));
                house.setPublishUser(rs.getString("publish_user"));
                house.setRemark(rs.getString("remark"));
                houses.add(house);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeConnection(conn, stmt, rs);
        }
        return houses;
    }

    public void deleteData(int id) {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = getConnection();
            String sql = "DELETE FROM house WHERE id = ?";
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, id);
            stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeConnection(conn, stmt, null);
        }
    }


    public void insertData(String address, Integer price, Integer area, String publishTime, String publishUser, String remark) {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = getConnection();
            String sql = "INSERT INTO house (address,price,area,publish_time,publish_user,remark) " +
                    "VALUES (?,?,?,?,?,?)";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, address);
            stmt.setInt(2, price);
            stmt.setInt(3, area);
            stmt.setString(4, publishTime);
            stmt.setString(5, publishUser);
            stmt.setString(6, remark);
            stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeConnection(conn, stmt, null);
        }
    }

    public void updateData(HouseEntity house) {
        Connection conn = null;
        PreparedStatement stmt = null;
        try {
            conn = getConnection();
            String sql = "UPDATE house SET" +
                    " address = ?," +
                    " price = ?," +
                    " area = ?," +
                    " publish_time = ?," +
                    " publish_user = ?," +
                    " remark = ?" +
                    " WHERE id = ?";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, house.getAddress());
            stmt.setInt(2, house.getPrice());
            stmt.setInt(3, house.getArea());
            stmt.setString(4, house.getPublishTime());
            stmt.setString(5, house.getPublishUser());
            stmt.setString(6, house.getRemark());
            stmt.setInt(7, house.getId());
            stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeConnection(conn, stmt, null);
        }
    }
}
